package com.liwen.kefu;

import com.liwen.kefu.service.ActionDataService;
import com.liwen.kefu.service.InitDataService;
import com.liwen.kefu.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author: Liwen
 * @createTime: 2024/11/1
 * @description:
 */

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class SingleTableAction {

    @Autowired
    private ActionDataService actionDataService;
    @Autowired
    private InitDataService initDataService;


    String dbName = "db_gmcf_ofc";
    String tableName = "t_ofc_loan_order";
    String mobileNo = "13655558110";
    String path = "E:\\gmf\\测试\\7.客服\\8.20241024_易卡、白条、普惠列表新增债转字段\\sql\\1.易卡";
    String modelName = "隐藏订单-新核心-";
    String fileName = "10." + modelName + mobileNo + "-" + dbName + "-" + tableName;
    String filePath = path + "\\" + fileName + ".xlsx";
    String userId = "";

    String type = "";


    /**
     * 根据手机号查询userId
     */
    @Before
    public void queryUserId() {
        userId = actionDataService.execHiveQueryUserId(mobileNo, type);
    }

    /**
     * hive查询
     */
    @Test
    public void hiveQueryTest() {
        String sql = initDataService.initQuerySql(userId, mobileNo, tableName);
        List<Map<String, Object>> list = actionDataService.execHiveQuery(sql, tableName);
        if (CollectionUtils.isEmpty(list)) {
            throw new NullPointerException("【hive 查询为空】==============================");
        }
        Map<String, String> map = initDataService.initMysqlTableName("");
        dbName = map.get(tableName);
        actionDataService.exportDataToExcel(filePath, list);
        actionDataService.excelImportDataToMysql(dbName, tableName, filePath);

    }

    /**
     * excel 导入mysql
     *
     * @throws IOException
     */
    @Test
    public void excelImportToMysqlTest() {
        actionDataService.excelImportDataToMysql(dbName, tableName, filePath);
        JdbcUtil.close();
    }

    /**
     * 读取excel 删除mysql
     */
    @Test
    public void deleteMysqlByExcelValueTest() {
        Map<String, String> map = initDataService.initMysqlTableName("");
        dbName = map.get(tableName);
        String key = initDataService.initKeys(dbName, tableName);
        String value = null;
        try {
            value = actionDataService.getDeleteKeysValue(filePath, key);
        } catch (IOException e) {
            e.printStackTrace();
            log.error("【根据key：{},获取excel value，失败了】=============================", key);
            System.exit(1);
        }
        try {
            actionDataService.execSqlDelete(dbName, tableName, key, value);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("【根据key：{},删除value：{}，失败了】=============================", key, value);
        }
        JdbcUtil.close();
    }


}
